<?php
/**
 * 
 * @author Elwin
 *
 */
class YiiPhpExport extends CDbActiveRecord
{
	public function init()
	{
		if (! empty ( $this->_template ))
		{
			$this->loadTemplate ();
		} else
		{
			$this->createTemplate ();
		}
	}
	public function template()
	{
	}
	public function save()
	{
	}
	public function loadTemplate($template = '')
	{
		if (! empty ( $template ))
			$this->_template = $template;
		
		if (! is_file ( $this->_path . $this->_template ))
			throw new Exception ( 'Unable to load template file: ' . $this->_path . $this->_template );
			
			// identify type of template file
		$fileTemlate = PHPExcel_IOFactory::identify ( $this->_path . $this->_template );
		// TODO: better control of allowed input types
		// load template file into PHPExcel objects
		$this->objReader = PHPExcel_IOFactory::createReader ( $fileTemlate );
		$this->objPHPExcel = $this->objReader->load ( $this->_path . $this->_template );
		$this->objWorksheet = $this->objPHPExcel->getActiveSheet ();
		
		$this->_using = true;
	}
	private function createTemplate()
	{
		$this->objPHPExcel = new PHPExcel ();
		$this->objPHPExcel->setActiveSheetIndex ( 0 );
		$this->objWorksheet = $this->objPHPExcel->getActiveSheet ();
		
		$this->_using = false;
	}
	public function generateReport()
	{
		foreach ( $this->_data as $data )
		{
			if (isset ( $data ['repeat'] ) && $data ['repeat'] == true)
			{
				// Repeating data
				$foundTags = false;
				$repeatRange = '';
				$firstRow = '';
				$lastRow = '';
				
				$firstCol = 'A'; // TODO: better detection
				$lastCol = $this->objWorksheet->getHighestColumn (); // TODO: better
				                                                  // detection
				                                                  
				// scan the template
				                                                  // search for
				                                                  // repeating part
				foreach ( $this->objWorksheet->getRowIterator () as $row )
				{
					$cellIterator = $row->getCellIterator ();
					$rowIndex = $row->getRowIndex ();
					// find the repeating range (one or more rows)
					foreach ( $cellIterator as $cell )
					{
						$cellval = trim ( $cell->getValue () );
						$column = $cell->getColumn ();
						// see if the cell has something for replacing
						if (preg_match_all ( "/\{" . $data ['id'] . ":(\w*|#\+?-?(\d*)?)\}/", $cellval, $matches ))
						{
							// this cell has replacement tags
							if (! $foundTags)
								$foundTags = true;
								// remember the first ant the last row
							if ($rowIndex != $firstRow)
								$lastRow = $rowIndex;
							if ($firstRow == '')
								$firstRow = $rowIndex;
						}
					}
				}
				
				// form the repeating range
				if ($foundTags)
					$repeatRange = $firstCol . $firstRow . ":" . $lastCol . $lastRow;
					
					// set initial format data
				if (! isset ( $data ['format'] ))
					$data ['format'] = array ();
					
					// check if data is an array
				if (is_array ( $data ['data'] ))
				{
					// every element is an array with data for all the columns
					if ($foundTags)
					{
						// insert repeating rows, as many as needed
						// check if grouping is defined
						if (count ( $this->_group ))
							$this->generateRepeatingRowsWithGrouping ( $data, $repeatRange );
						else
							$this->generateRepeatingRows ( $data, $repeatRange );
							// remove the template rows
						for($i = $firstRow; $i <= $lastRow; $i ++)
						{
							$this->objWorksheet->removeRow ( $firstRow );
						}
						// if there is no data
						if (count ( $data ['data'] ) == 0)
							$this->addNoResultRow ( $firstRow, $firstCol, $lastCol );
					}
				} else
				{
					// TODO
					// maybe an SQL query?
					// needs to be database agnostic
				}
			} else
			{
				// non-repeating data
				// check for additional formating
				if (! isset ( $data ['format'] ))
					$data ['format'] = array ();
					
					// check if data is an array or mybe a SQL query
				if (is_array ( $data ['data'] ))
				{
					// array of data
					$this->generateSingleRow ( $data );
				} else
				{
					// TODO
					// maybe an SQL query?
					// needs to be database agnostic
				}
			}
		}
		
		// call the replacing function
		$this->searchAndReplace ();
		
		// generate heading if heading text is set
		if ($this->_headingText != '')
			$this->generateHeading ();
	}
	
	public function createReport()
	{
		foreach($this->_data as $data)
		{
			//$data must have id and data elements
			//$data may also have config, header, footer, group
	
			$id=$data['id'];
			$format=isset($data['format'])?$data['format']:array();
			$config=isset($data['config'])?$data['config']:array();
			$group=isset($data['group'])?$data['group']:array();
	
			$configHeader=isset($config['header'])?$config['header']:$config;
			$configData=isset($config['data'])?$config['data']:$config;
			$configFooter=isset($config['footer'])?$config['footer']:$config;
	
			$config=array(
					'header'=>$configHeader,
					'data'=>$configData,
					'footer'=>$configFooter
			);
	
			//set the group
			$this->_group=$group;
	
			$loadCollection=array();
	
			$nextRow=$this->objWorksheet->getHighestRow();
			if($nextRow>1)
				$nextRow++;
			$colIndex=-1;
	
			//form the header for data
			if(isset($data['header']))
			{
				$headerId='HEADER_'.$id;
				foreach($data['header'] as $k=>$v)
				{
					$colIndex++;
					$tag="{".$headerId.":".$k."}";
					$this->objWorksheet->setCellValueByColumnAndRow($colIndex,$nextRow,$tag);
					if(isset($config['header'][$k]['width']))
						$this->objWorksheet->getColumnDimensionByColumn($colIndex)->setWidth(pixel2unit($config['header'][$k]['width']));
					if(isset($config['header'][$k]['align']))
						$this->objWorksheet->getStyleByColumnAndRow($colIndex,$nextRow)->getAlignment()->setHorizontal($config['header'][$k]['align']);
				}
	
				if($colIndex>-1)
				{
					$this->objWorksheet->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).$nextRow.':'.PHPExcel_Cell::stringFromColumnIndex($colIndex).$nextRow)->applyFromArray($this->_headerStyleArray);
				}
	
				//add header row to load collection
				$loadCollection[]=array('id'=>$headerId,'data'=>$data['header']);
	
				//move to next row for data
				$nextRow++;
			}
	
	
			//form the data repeating row
			$dataId='DATA_'.$id;
			$colIndex=-1;
	
			//form the template row
			if(count($data['data'])>0)
			{
				//we just need first row of data, to see array keys
				$singleDataRow=$data['data'][0];
				foreach($singleDataRow as $k=>$v)
				{
					$colIndex++;
					$tag="{".$dataId.":".$k."}";
					$this->objWorksheet->setCellValueByColumnAndRow($colIndex,$nextRow,$tag);
					if(isset($config['data'][$k]['align']))
						$this->objWorksheet->getStyleByColumnAndRow($colIndex,$nextRow)->getAlignment()->setHorizontal($config['data'][$k]['align']);
				}
			}
	
			//add this row to collection for load but with repeating
			$loadCollection[]=array('id'=>$dataId,'data'=>$data['data'],'repeat'=>true,'format'=>$format);
			$this->enableStripRows();
	
			//form the footer row for data if needed
			if(isset($data['footer']))
			{
				$footerId='FOOTER_'.$id;
				$colIndex=-1;
				$nextRow++;
	
				//formiraj template
				foreach($data['footer'] as $k=>$v)
				{
					$colIndex++;
					$tag="{".$footerId.":".$k."}";
					$this->objWorksheet->setCellValueByColumnAndRow($colIndex,$nextRow,$tag);
					if(isset($config['footer'][$k]['align']))
						$this->objWorksheet->getStyleByColumnAndRow($colIndex,$nextRow)->getAlignment()->setHorizontal($config['footer'][$k]['align']);
				}
				if($colIndex>-1)
				{
					$this->objWorksheet->getStyle(PHPExcel_Cell::stringFromColumnIndex(0).$nextRow.':'.PHPExcel_Cell::stringFromColumnIndex($colIndex).$nextRow)->applyFromArray($this->_footerStyleArray);
				}
	
				//add footer row to load collection
				$loadCollection[]=array('id'=>$footerId,'data'=>$data['footer'],'format'=>$format);
			}
	
			$this->load($loadCollection);
			$this->generateReport();
		}
	}
	public function load($dataCollection)
	{
		if(!is_array($dataCollection))
			throw new Exception("Could not load a non-array data!");
	
		//clear current data
		$this->clearData();
	
		//check if it is a single array of data
		if(isset ($dataCollection['data']))
		{
			$this->addData($dataCollection);
		}
		else
		{
			//it's an array of arrays of data, add all
			foreach($dataCollection as $data)
				$this->addData($data);
		}
	}
	private function searchAndReplace()
	{
		foreach ($this->objWorksheet->getRowIterator() as $row)
		{
			$cellIterator = $row->getCellIterator();
			foreach ($cellIterator as $cell)
			{
				$cell->setValue(str_replace($this->_search, $this->_replace, $cell->getValue()));
			}
		}
	}
	
	public function render($type = 'html', $filename = '')
	{
		// create or generate report
		if ($this->_using)
			$this->generateReport ();
		else
			$this->createReport ();
		
		if ($type == '')
			$type = "html";
		
		if ($filename == '')
			$filename = "Report " . date ( "Y-m-d" );
		else
			$filename = strftime ( $filename );
			// http://strftime.net/
		
		switch (strtolower ( $type ))
		{
			case 'html' :
				return $this->renderHtml ();
				break;
			
			case 'excel5' :
				return $this->renderXls ( $filename );
				break;
			
			case 'excel2007' :
				return $this->renderXlsx ( $filename );
				break;
			
			case 'pdf' :
				return $this->renderPdf ( $filename );
				break;
			
			default :
				return "Error: unsupported export type!"; // TODO: better error
				                                          // handling
				break;
		}
	}
	
	/**
	 * Renders report as a HTML output
	 */
	protected function renderHtml()
	{
		$this->objWriter = new PHPExcel_Writer_HTML ( $this->objPHPExcel );
		
		// Generate HTML
		$html = '';
		$html .= $this->objWriter->generateHTMLHeader ( true );
		$html .= $this->objWriter->generateSheetData ();
		$html .= $this->objWriter->generateHTMLFooter ();
		$html .= '';
		$this->objPHPExcel->disconnectWorkSheets ();
		unset ( $this->objWriter );
		unset ( $this->objWorksheet );
		unset ( $this->objReader );
		unset ( $this->objPHPExcel );
		
		return $html;
	}
	
	/**
	 * Renders report as a XLSX file
	 */
	protected function renderXlsx($filename)
	{
		header ( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
		header ( 'Content-Disposition: attachment;filename="' . $filename . '.xlsx"' );
		header ( 'Cache-Control: max-age=0' );
		
		$this->objWriter = PHPExcel_IOFactory::createWriter ( $this->objPHPExcel, 'Excel2007' );
		
		$this->objWriter->save ( 'php://output' );
		unset ( $this->objWriter );
		unset ( $this->objWorksheet );
		unset ( $this->objReader );
		unset ( $this->objPHPExcel );
		exit ();
	}
	
	/**
	 * Renders report as a XLS file
	 */
	protected function renderXls($filename)
	{
		header ( 'Content-Type: application/vnd.ms-excel' );
		header ( 'Content-Disposition: attachment;filename="' . $filename . '.xls"' );
		header ( 'Cache-Control: max-age=0' );
		
		$this->objWriter = PHPExcel_IOFactory::createWriter ( $this->objPHPExcel, 'Excel5' );
		
		$this->objWriter->save ( 'php://output' );
		unset ( $this->objWriter );
		unset ( $this->objWorksheet );
		unset ( $this->objReader );
		unset ( $this->objPHPExcel );
		exit ();
	}
	
	/**
	 * Renders report as a PDF file
	 */
	protected function renderPdf($filename)
	{
		header ( 'Content-Type: application/vnd.pdf' );
		header ( 'Content-Disposition: attachment;filename="' . $filename . '.pdf"' );
		header ( 'Cache-Control: max-age=0' );
		
		$this->objWriter = PHPExcel_IOFactory::createWriter ( $this->objPHPExcel, 'PDF' );
		
		$this->objWriter->save ( 'php://output' );
		exit ();
	}
}
?>